﻿
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sproc_Flow_GetDocumentPostil]') AND type in (N'P', N'PC'))
    DROP PROCEDURE [dbo].[sproc_Flow_GetDocumentPostil];
GO
CREATE PROCEDURE [dbo].[sproc_Flow_GetDocumentPostil]
    @DocID int
AS
DECLARE @BuildTime datetime

IF EXISTS( SELECT 1 FROM dbo.uds_flow_document WHERE doc_id = @docid )
BEGIN
    SELECT @BuildTime = doc_added_date FROM dbo.uds_flow_document WHERE DOC_ID = @DocID

    SELECT
        IDENTITY(INT,1,1) AS ID,
        DOC_ID,
        POSTILER_ID,
        POSTIL_DATE,
        POSTIL_CONTENT,
        POSTIL_TYPE,
        STEP_ID,
        [FILE_ID],
        OBJ_ID,
        OBJ_TYPE
    INTO #T
    FROM dbo.uds_flow_postil
    WHERE doc_id = @docid 
    ORDER BY postil_date 

    SELECT 
        b.realname,
        a.postil_content,
        a.postil_date,
        a.postil_type,
        a.usedtime,
        c.*
        FROM 
            (
                SELECT
                    m.*,
                    (
                        CASE 
                            WHEN n.ID IS NULL THEN DATEDIFF(N,@BuildTime,m.postil_date) 
                            ELSE DATEDIFF(N,n.postil_date,m.postil_date) 
                        END
                    ) AS UsedTime 
                FROM #T m LEFT OUTER JOIN #T n ON m.ID = n.ID +1
            ) a
            INNER JOIN dbo.uds_staff b ON a.Postiler_ID = b.staff_id
            LEFT OUTER JOIN dbo.uds_flow_files c ON a.[file_id] = c.fileid
        WHERE
            a.doc_id = @docid
        ORDER BY a.postil_date

    DROP TABLE #T
END
ELSE
BEGIN
    PRINT '文档不存在！'
END;
GO